package leagueapp;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author a
 */
public class Database {

    public static Connection con = null;
    public static Statement pst = null;
    public static String user = "root";
    public static String password = "";

    public static void connect() {
        String url = "jdbc:mysql://localhost/Siatka";
        try {
            con = DriverManager.getConnection(url, user, password);
            pst = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void disconnect() {
        try {
            if (pst != null) {
                pst.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }

    public static ArrayList getTeamList(String aCountry) {
        ArrayList teamList = new ArrayList();
        Team team;
        if (!aCountry.isEmpty()) {
            int counter = SelectInt("SELECT Count(*) FROM Zespoly WHERE Kraj='" + aCountry + "'");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                String name = SelectString("SELECT Nazwa FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                String league = SelectString("SELECT Liga FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT Sezon FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int matchesPlayed = SelectInt("SELECT Mecze_rozegrane FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int matchesWon = SelectInt("SELECT Mecze_wygrane FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int matchesLost = SelectInt("SELECT Mecze_przegrane FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int points = SelectInt("SELECT Punkty FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int setsWon = SelectInt("SELECT Sety_wygrane FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int setsLost = SelectInt("SELECT Sety_przegrane FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int pointsWon = SelectInt("SELECT Punkty_zdobyte FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                int pointsLost = SelectInt("SELECT Punkty_stracone FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
                team = new Team(id, name, aCountry, league, year, matchesPlayed, matchesWon, matchesLost, points, setsWon, setsLost, pointsWon, pointsLost);
                teamList.add(team);
            }
        } else {
            int counter = SelectInt("SELECT Count(*) FROM Zespoly");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zespoly LIMIT " + i + "," + 1);
                String name = SelectString("SELECT Nazwa FROM Zespoly LIMIT " + i + "," + 1);
                String country = SelectString("SELECT Kraj FROM Zespoly LIMIT " + i + "," + 1);
                String league = SelectString("SELECT Liga FROM Zespoly LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT Sezon FROM Zespoly LIMIT " + i + "," + 1);
                int matchesPlayed = SelectInt("SELECT Mecze_rozegrane FROM Zespoly LIMIT " + i + "," + 1);
                int matchesWon = SelectInt("SELECT Mecze_wygrane FROM Zespoly LIMIT " + i + "," + 1);
                int matchesLost = SelectInt("SELECT Mecze_przegrane FROM Zespoly LIMIT " + i + "," + 1);
                int points = SelectInt("SELECT Punkty FROM Zespoly LIMIT " + i + "," + 1);
                int setsWon = SelectInt("SELECT Sety_wygrane FROM Zespoly LIMIT " + i + "," + 1);
                int setsLost = SelectInt("SELECT Sety_przegrane FROM Zespoly LIMIT " + i + "," + 1);
                int pointsWon = SelectInt("SELECT Punkty_zdobyte FROM Zespoly LIMIT " + i + "," + 1);
                int pointsLost = SelectInt("SELECT Punkty_stracone FROM Zespoly LIMIT " + i + "," + 1);
                team = new Team(id, name, country, league, year, matchesPlayed, matchesWon, matchesLost, points, setsWon, setsLost, pointsWon, pointsLost);
                teamList.add(team);
            }
        }
        return teamList;
    }

    public static ArrayList getTeamList() {
        ArrayList teamList = new ArrayList();
        Team team;
        int counter = SelectInt("SELECT Count(*) FROM Zespoly");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Zespoly LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Nazwa FROM Zespoly LIMIT " + i + "," + 1);
            team = new Team(id, name);
            teamList.add(team);
        }
        return teamList;
    }

    public static ArrayList getPlayerList(String aName, String aSurname) {
        ArrayList playerList = new ArrayList();
        Player player;
        if (!(aName.isEmpty() || aSurname.isEmpty())) {
            int counter = SelectInt("SELECT Count(*) FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "'");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String nationality = SelectString("SELECT Narodowosc FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String position = SelectString("SELECT Pozycja FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int number = SelectInt("SELECT Numer FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int clubId = SelectInt("SELECT Id_Klubu FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
                int height = SelectInt("SELECT Wzrost FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int weight = SelectInt("SELECT Waga FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int attRange = SelectInt("SELECT Zasieg_atak FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int defRange = SelectInt("SELECT Zasieg_obrona FROM Zawodnicy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                player = new Player(id, aName, aSurname, day, month, year, nationality, position, number, club, clubId, height, weight, attRange, defRange);
                playerList.add(player);
            }
        } else if (!aName.isEmpty()) {
            int counter = SelectInt("SELECT Count(*) FROM Zawodnicy WHERE Imie='" + aName + "'");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                String surname = SelectString("SELECT Nazwisko FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                String nationality = SelectString("SELECT Narodowosc FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                String position = SelectString("SELECT Pozycja FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int number = SelectInt("SELECT Numer FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int clubId = SelectInt("SELECT Id_Klubu FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
                int height = SelectInt("SELECT Wzrost FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int weight = SelectInt("SELECT Waga FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int attRange = SelectInt("SELECT Zasieg_atak FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                int defRange = SelectInt("SELECT Zasieg_obrona FROM Zawodnicy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
                player = new Player(id, aName, surname, day, month, year, nationality, position, number, club, clubId, height, weight, attRange, defRange);
                playerList.add(player);
            }
        } else if (!aSurname.isEmpty()) {
            int counter = SelectInt("SELECT Count(*) FROM Zawodnicy WHERE Nazwisko='" + aSurname + "'");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String name = SelectString("SELECT Imie FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String nationality = SelectString("SELECT Narodowosc FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String position = SelectString("SELECT Pozycja FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int number = SelectInt("SELECT Numer FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int clubId = SelectInt("SELECT Id_Klubu FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
                int height = SelectInt("SELECT Wzrost FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int weight = SelectInt("SELECT Waga FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int attRange = SelectInt("SELECT Zasieg_atak FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                int defRange = SelectInt("SELECT Zasieg_obrona FROM Zawodnicy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
                player = new Player(id, name, aSurname, day, month, year, nationality, position, number, club, clubId, height, weight, attRange, defRange);
                playerList.add(player);
            }
        } else {
            int counter = SelectInt("SELECT Count(*) FROM Zawodnicy");
            for (int i = 0; i < counter; i++) {
                int id = SelectInt("SELECT Id FROM Zawodnicy LIMIT " + i + "," + 1);
                String name = SelectString("SELECT Imie FROM Zawodnicy LIMIT " + i + "," + 1);
                String surname = SelectString("SELECT Nazwisko FROM Zawodnicy LIMIT " + i + "," + 1);
                int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Zawodnicy LIMIT " + i + "," + 1);
                int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Zawodnicy LIMIT " + i + "," + 1);
                int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Zawodnicy LIMIT " + i + "," + 1);
                String nationality = SelectString("SELECT Narodowosc FROM Zawodnicy LIMIT " + i + "," + 1);
                String position = SelectString("SELECT Pozycja FROM Zawodnicy LIMIT " + i + "," + 1);
                int number = SelectInt("SELECT Numer FROM Zawodnicy LIMIT " + i + "," + 1);
                int clubId = SelectInt("SELECT Id_Klubu FROM Zawodnicy LIMIT " + i + "," + 1);
                String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
                int height = SelectInt("SELECT Wzrost FROM Zawodnicy LIMIT " + i + "," + 1);
                int weight = SelectInt("SELECT Waga FROM Zawodnicy LIMIT " + i + "," + 1);
                int attRange = SelectInt("SELECT Zasieg_atak FROM Zawodnicy LIMIT " + i + "," + 1);
                int defRange = SelectInt("SELECT Zasieg_obrona FROM Zawodnicy LIMIT " + i + "," + 1);
                player = new Player(id, name, surname, day, month, year, nationality, position, number, club, clubId, height, weight, attRange, defRange);
                playerList.add(player);
            }
        }
        return playerList;
    }

    public static ArrayList getHallList(String aCountry) {
        ArrayList hallList = new ArrayList();
        Hall hall;
        if (!aCountry.isEmpty()) {
        int counter = SelectInt("SELECT Count(*) FROM Hale WHERE Kraj='" + aCountry + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Hale WHERE Kraj='" + aCountry + "' LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Nazwa FROM Hale WHERE Kraj='" + aCountry + "' LIMIT " + i + "," + 1);
            String address = SelectString("SELECT Ulica FROM Hale WHERE Kraj='" + aCountry + "' LIMIT " + i + "," + 1);
            String city = SelectString("SELECT Miasto FROM Hale WHERE Kraj='" + aCountry + "' LIMIT " + i + "," + 1);
            int capacity = SelectInt("SELECT Pojemnosc FROM Hale WHERE Kraj='" + aCountry + "' LIMIT " + i + "," + 1);
            hall = new Hall(id, name, address, city, aCountry, capacity);
            hallList.add(hall);
        }
        } else {
            int counter = SelectInt("SELECT Count(*) FROM Hale");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Hale LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Nazwa FROM Hale LIMIT " + i + "," + 1);
            String address = SelectString("SELECT Ulica FROM Hale LIMIT " + i + "," + 1);
            String city = SelectString("SELECT Miasto FROM Hale LIMIT " + i + "," + 1);
            String country = SelectString("SELECT Kraj FROM Hale LIMIT " + i + "," + 1);
            int capacity = SelectInt("SELECT Pojemnosc FROM Hale LIMIT " + i + "," + 1);
            hall = new Hall(id, name, address, city, country, capacity);
            hallList.add(hall);
        }
        }
        return hallList;
    }

    public static ArrayList getCoachList(String aName, String aSurname) {
        ArrayList coachList = new ArrayList();
        Coach coach;
        if (!(aName.isEmpty() || aSurname.isEmpty())) {
        int counter = SelectInt("SELECT Count(*) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int clubId = SelectInt("SELECT Id_Klubu FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
            String job = SelectString("SELECT Posada FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            coach = new Coach(id, aName, aSurname, day, month, year, nationality, club, clubId, job);
            coachList.add(coach);
        }
        } else if(!aName.isEmpty()){
            int counter = SelectInt("SELECT Count(*) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int clubId = SelectInt("SELECT Id_Klubu FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
            String job = SelectString("SELECT Posada FROM Sztab_szkoleniowy WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            coach = new Coach(id, aName, surname, day, month, year, nationality, club, clubId, job);
            coachList.add(coach);
        }
        } else if(!aSurname.isEmpty()){
            int counter = SelectInt("SELECT Count(*) FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int clubId = SelectInt("SELECT Id_Klubu FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
            String job = SelectString("SELECT Posada FROM Sztab_szkoleniowy WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            coach = new Coach(id, name, aSurname, day, month, year, nationality, club, clubId, job);
            coachList.add(coach);
        }
        } else {
            int counter = SelectInt("SELECT Count(*) FROM Sztab_szkoleniowy");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            int clubId = SelectInt("SELECT Id_Klubu FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id=" + clubId);
            String job = SelectString("SELECT Posada FROM Sztab_szkoleniowy LIMIT " + i + "," + 1);
            coach = new Coach(id, name, surname, day, month, year, nationality, club, clubId, job);
            coachList.add(coach);
        }
        }
        return coachList;
    }

    public static ArrayList getRefereeList(String aName, String aSurname) {
        ArrayList refereeList = new ArrayList();
        Referee referee;
        if (!(aName.isEmpty() || aSurname.isEmpty())) {
        int counter = SelectInt("SELECT Count(*) FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sedziowie WHERE Imie='" + aName + "' AND Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            referee = new Referee(id, aName, aSurname, day, month, year, nationality);
            refereeList.add(referee);
        }
        } else if(!aName.isEmpty()){
            int counter = SelectInt("SELECT Count(*) FROM Sedziowie WHERE Imie='" + aName + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);           
            String surname = SelectString("SELECT Nazwisko FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sedziowie WHERE Imie='" + aName + "' LIMIT " + i + "," + 1);
            referee = new Referee(id, aName, surname, day, month, year, nationality);
            refereeList.add(referee);
        } 
        } else if(!aSurname.isEmpty()){
            int counter = SelectInt("SELECT Count(*) FROM Sedziowie WHERE Nazwisko='" + aSurname + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sedziowie WHERE Nazwisko='" + aSurname + "' LIMIT " + i + "," + 1);
            referee = new Referee(id, name, aSurname, day, month, year, nationality);
            refereeList.add(referee);
        }
        } else{
            int counter = SelectInt("SELECT Count(*) FROM Sedziowie");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sedziowie LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sedziowie LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Sedziowie LIMIT " + i + "," + 1);
            int day = SelectInt("SELECT DAY(Data_urodzenia) FROM Sedziowie LIMIT " + i + "," + 1);
            int month = SelectInt("SELECT MONTH(Data_urodzenia) FROM Sedziowie LIMIT " + i + "," + 1);
            int year = SelectInt("SELECT YEAR(Data_urodzenia) FROM Sedziowie LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Sedziowie LIMIT " + i + "," + 1);
            referee = new Referee(id, name, surname, day, month, year, nationality);
            refereeList.add(referee);
        }
        }
        return refereeList;
    }

    public static void updateTeam(int aId, String aName, String aCountry, String aLeague, int aYear, int aMatchesPlayed, int aMatchesWon, int aMatchesLost, int aPoints, int aSetsWon, int aSetsLost, int aPointsWon, int aPointsLost) {
        connect();
        String query = "Update Zespoly SET Nazwa='" + aName + "', Kraj='" + aCountry + "', Liga='" + aLeague + "', Sezon=" + aYear + ", Mecze_rozegrane=" + aMatchesPlayed + ", Mecze_wygrane=" + aMatchesWon + ", Mecze_przegrane=" + aMatchesLost + ", Punkty=" + aPoints + ", Sety_wygrane=" + aSetsWon + ", Sety_przegrane=" + aSetsLost + ", Punkty_zdobyte=" + aPointsWon + ", Punkty_stracone=" + aPointsLost + " WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public static void updatePlayer(int aId, String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality, String aPosition, int aNumber, int aClubId, int aHeight, int aWeight, int aAttRange, int aDefRange) {
        connect();
        String query = "Update Zawodnicy SET Imie='" + aName + "', Nazwisko='" + aSurname + "', Data_urodzenia='" + aYear + "-" + aMonth + "-" + aDay + "', Narodowosc='" + aNationality + "', Pozycja='" + aPosition + "', Numer=" + aNumber + ", Id_Klubu=" + aClubId + ", Wzrost=" + aHeight + ", Waga=" + aWeight + ", Zasieg_atak=" + aAttRange + ", Zasieg_obrona=" + aDefRange + " WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void updateCoach(int aId, String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality, int aClubId, String aJob) {
        connect();
        String query = "Update Sztab_szkoleniowy SET Imie='" + aName + "', Nazwisko='" + aSurname + "', Data_urodzenia='" + aYear + "-" + aMonth + "-" + aDay + "', Narodowosc='" + aNationality + "', Id_Klubu=" + aClubId + ", Posada='" + aJob + "' WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void updateReferee(int aId, String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality) {
        connect();
        String query = "Update Sedziowie SET Imie='" + aName + "', Nazwisko='" + aSurname + "', Data_urodzenia='" + aYear + "-" + aMonth + "-" + aDay + "', Narodowosc='" + aNationality + "' WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void updateHall(int aId, String aName, String aAddress, String aCity, String aCountry, int aCapacity) {
        connect();
        String query = "Update Hale SET Nazwa='" + aName + "', Ulica='" + aAddress + "', Miasto='" + aCity + "', Kraj='" + aCountry + "', Pojemnosc=" + aCapacity + " WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public static void deleteTeam(int aId) {
        connect();
        String query = "Delete z.* FROM Zespoly z WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public static void deletePlayer(int aId) {
        connect();
        String query = "Delete z.* FROM Zawodnicy z WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void deleteCoach(int aId) {
        connect();
        String query = "Delete ss.* FROM Sztab_szkoleniowy ss WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void deleteReferee(int aId) {
        connect();
        String query = "Delete s.* FROM Sedziowie s WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void deleteHall(int aId) {
        connect();
        String query = "Delete h.* FROM Hale h WHERE Id=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public static void deletePlayersFromTeam(int aId) {
        connect();
        String query = "UPDATE Zawodnicy SET ID_Klubu=null WHERE ID_Klubu=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
        deleteCoachesFromTeam(aId);
    }

    public static void deleteCoachesFromTeam(int aId) {
        connect();
        String query = "UPDATE Sztab_szkoleniowy SET ID_Klubu=null WHERE ID_Klubu=" + aId;
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
        deleteTeam(aId);
    }
    
    public static void addTeam(String aName, String aCountry, String aLeague, int aYear, int aMatchesPlayed, int aMatchesWon, int aMatchesLost, int aPoints, int aSetsWon, int aSetsLost, int aPointsWon, int aPointsLost){
        connect();
        String query ="INSERT INTO Zespoly(Nazwa,Kraj,Liga,Sezon,Mecze_rozegrane,Mecze_wygrane,Mecze_przegrane,Punkty,Sety_wygrane,Sety_przegrane,Punkty_zdobyte,Punkty_stracone) values ('" + aName + "', '" + aCountry + "', '" + aLeague + "', " + aYear + ", " + aMatchesPlayed + ", " + aMatchesWon + ", " + aMatchesLost + ", " + aPoints + ", " + aSetsWon + ", " + aSetsLost + "," + aPointsWon + "," + aPointsLost + ")";
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void addPlayer(String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality, String aPosition, int aNumber, int aClubId, int aHeight, int aWeight, int aAttRange, int aDefRange){
        connect();
        String query ="INSERT INTO Zawodnicy(Imie,Nazwisko,Data_urodzenia,Narodowosc,Pozycja,Numer,Id_Klubu,Wzrost,Waga,Zasieg_atak,Zasieg_obrona) values ('" + aName + "', '" + aSurname + "', '" + aYear + "-" + aMonth + "-" + aDay + "', '" + aNationality + "', '" + aPosition + "', " + aNumber + ", " + aClubId + ", " + aHeight + ", " + aWeight + ", " + aAttRange + ", " + aDefRange + ")";
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void addCoach(String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality, int aClubId, String aJob){
        connect();
        String query ="INSERT INTO Sztab_szkoleniowy(Imie,Nazwisko,Data_urodzenia,Narodowosc,Id_Klubu,Posada) values ('" + aName + "', '" + aSurname + "', '" + aYear + "-" + aMonth + "-" + aDay + "', '" + aNationality + "', " + aClubId + ", '" + aJob + "')";
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void addReferee(String aName, String aSurname, int aDay, int aMonth, int aYear, String aNationality){
        connect();
        String query ="INSERT INTO Sedziowie(Imie,Nazwisko,Data_urodzenia,Narodowosc) values ('" + aName + "', '" + aSurname + "', '" + aYear + "-" + aMonth + "-" + aDay + "', '" + aNationality + "')";
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public static void addHall(String aName, String aAddress, String aCity, String aCountry, int aCapacity){
        connect();
        String query ="INSERT INTO Hale(Nazwa,Ulica,Miasto,Kraj,Pojemnosc) values ('" + aName + "', '" + aAddress + "', '" + aCity + "', '" + aCountry + "', " + aCapacity + ")";
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public static int SelectInt(String query) {
        ResultSet rs;
        int result = 0;
        connect();
        try {
            rs = pst.executeQuery(query);
            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } finally {
            disconnect();
        }
        return result;
    }

    public static String SelectString(String query) {
        ResultSet rs;
        String result = null;
        connect();
        try {
            rs = pst.executeQuery(query);
            while (rs.next()) {
                result = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } finally {
            disconnect();
        }
        return result;
    }
}
